{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load market data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>交易日</th>\n",
       "      <th>合约代码</th>\n",
       "      <th>交易所代码</th>\n",
       "      <th>合约在交易所的代码</th>\n",
       "      <th>最新价</th>\n",
       "      <th>上次结算价</th>\n",
       "      <th>昨收盘</th>\n",
       "      <th>昨持仓量</th>\n",
       "      <th>今开盘</th>\n",
       "      <th>最高价</th>\n",
       "      <th>...</th>\n",
       "      <th>申买价四</th>\n",
       "      <th>申买量四</th>\n",
       "      <th>申卖价四</th>\n",
       "      <th>申卖量四</th>\n",
       "      <th>申买价五</th>\n",
       "      <th>申买量五</th>\n",
       "      <th>申卖价五</th>\n",
       "      <th>申卖量五</th>\n",
       "      <th>当日均价</th>\n",
       "      <th>业务日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3388</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3388</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33880.0</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3386</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33878.3</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3383</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33856.0</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3385</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33848.9</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3383</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33826.3</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26939</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.3</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26940</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3337</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26941</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3336</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26942</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26943</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26944 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            交易日    合约代码 交易所代码 合约在交易所的代码   最新价  上次结算价   昨收盘     昨持仓量   今开盘  \\\n",
       "0      20200228  rb2005  SHFE            3388   3426  3419  1512736  3388   \n",
       "1      20200228  rb2005  SHFE            3386   3426  3419  1512736  3388   \n",
       "2      20200228  rb2005  SHFE            3383   3426  3419  1512736  3388   \n",
       "3      20200228  rb2005  SHFE            3385   3426  3419  1512736  3388   \n",
       "4      20200228  rb2005  SHFE            3383   3426  3419  1512736  3388   \n",
       "...         ...     ...   ...       ...   ...    ...   ...      ...   ...   \n",
       "26939  20200228  rb2005  SHFE            3335   3426  3419  1512736  3388   \n",
       "26940  20200228  rb2005  SHFE            3337   3426  3419  1512736  3388   \n",
       "26941  20200228  rb2005  SHFE            3336   3426  3419  1512736  3388   \n",
       "26942  20200228  rb2005  SHFE            3335   3426  3419  1512736  3388   \n",
       "26943  20200228  rb2005  SHFE            3335   3426  3419  1512736  3388   \n",
       "\n",
       "        最高价  ...  申买价四  申买量四  申卖价四  申卖量四  申买价五  申买量五  申卖价五  申卖量五     当日均价  \\\n",
       "0      3388  ...     0     0     0     0     0     0     0     0  33880.0   \n",
       "1      3389  ...     0     0     0     0     0     0     0     0  33878.3   \n",
       "2      3389  ...     0     0     0     0     0     0     0     0  33856.0   \n",
       "3      3389  ...     0     0     0     0     0     0     0     0  33848.9   \n",
       "4      3389  ...     0     0     0     0     0     0     0     0  33826.3   \n",
       "...     ...  ...   ...   ...   ...   ...   ...   ...   ...   ...      ...   \n",
       "26939  3393  ...     0     0     0     0     0     0     0     0  33549.3   \n",
       "26940  3393  ...     0     0     0     0     0     0     0     0  33549.2   \n",
       "26941  3393  ...     0     0     0     0     0     0     0     0  33549.2   \n",
       "26942  3393  ...     0     0     0     0     0     0     0     0  33549.2   \n",
       "26943  3393  ...     0     0     0     0     0     0     0     0  33549.2   \n",
       "\n",
       "           业务日期  \n",
       "0      20200228  \n",
       "1      20200228  \n",
       "2      20200228  \n",
       "3      20200228  \n",
       "4      20200228  \n",
       "...         ...  \n",
       "26939  20200228  \n",
       "26940  20200228  \n",
       "26941  20200228  \n",
       "26942  20200228  \n",
       "26943  20200228  \n",
       "\n",
       "[26944 rows x 44 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('rb2005_20200228.csv', encoding='gb2312')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['交易日', '合约代码', '交易所代码', '合约在交易所的代码', '最新价', '上次结算价', '昨收盘', '昨持仓量',\n",
       "       '今开盘', '最高价', '最低价', '数量', '成交金额', '持仓量', '今收盘', '本次结算价', '涨停板价',\n",
       "       '跌停板价', '昨虚实度', '今虚实度', '最后修改时间', '最后修改毫秒', '申买价一', '申买量一', '申卖价一',\n",
       "       '申卖量一', '申买价二', '申买量二', '申卖价二', '申卖量二', '申买价三', '申买量三', '申卖价三', '申卖量三',\n",
       "       '申买价四', '申买量四', '申卖价四', '申卖量四', '申买价五', '申买量五', '申卖价五', '申卖量五', '当日均价',\n",
       "       '业务日期'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = ['trade_date', 'symbol','exchange','symbol_in_exchange', 'last_price','prev_settle','prev_close','prev_open_interest',\n",
    "              'open','high','low','volume','turnover','open_interest','close','settle','limit_up',\n",
    "              'limit_down','prev_otm','otm','time','millisecond','bid_price1','bid_volume1','ask_price1','ask_volume1',\n",
    "              'bid_price2','bid_volume2','ask_price2','ask_volume2','bid_price3','bid_volume4','ask_price3','ask_volume3',\n",
    "              'bid_price4','bid_volume4','ask_price4','ask_volume4','bid_price5','bid_volume5','ask_price5','ask_volume5',\n",
    "              'avg_price','date'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['avg_price2'] = df.turnover / df.volume"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>symbol</th>\n",
       "      <th>exchange</th>\n",
       "      <th>symbol_in_exchange</th>\n",
       "      <th>last_price</th>\n",
       "      <th>prev_settle</th>\n",
       "      <th>prev_close</th>\n",
       "      <th>prev_open_interest</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>...</th>\n",
       "      <th>bid_volume4</th>\n",
       "      <th>ask_price4</th>\n",
       "      <th>ask_volume4</th>\n",
       "      <th>bid_price5</th>\n",
       "      <th>bid_volume5</th>\n",
       "      <th>ask_price5</th>\n",
       "      <th>ask_volume5</th>\n",
       "      <th>avg_price</th>\n",
       "      <th>date</th>\n",
       "      <th>avg_price2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3388</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3388</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33880.0</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33880.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3386</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33878.3</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33878.252678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3383</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33856.0</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33856.020442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3385</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33848.9</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33848.874663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3383</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33826.3</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33826.327174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26939</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.3</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.286281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26940</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3337</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.238186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26941</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3336</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.222447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26942</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.205874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26943</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>SHFE</td>\n",
       "      <td></td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.205874</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26944 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       trade_date  symbol exchange symbol_in_exchange  last_price  \\\n",
       "0        20200228  rb2005     SHFE                           3388   \n",
       "1        20200228  rb2005     SHFE                           3386   \n",
       "2        20200228  rb2005     SHFE                           3383   \n",
       "3        20200228  rb2005     SHFE                           3385   \n",
       "4        20200228  rb2005     SHFE                           3383   \n",
       "...           ...     ...      ...                ...         ...   \n",
       "26939    20200228  rb2005     SHFE                           3335   \n",
       "26940    20200228  rb2005     SHFE                           3337   \n",
       "26941    20200228  rb2005     SHFE                           3336   \n",
       "26942    20200228  rb2005     SHFE                           3335   \n",
       "26943    20200228  rb2005     SHFE                           3335   \n",
       "\n",
       "       prev_settle  prev_close  prev_open_interest  open  high  ...  \\\n",
       "0             3426        3419             1512736  3388  3388  ...   \n",
       "1             3426        3419             1512736  3388  3389  ...   \n",
       "2             3426        3419             1512736  3388  3389  ...   \n",
       "3             3426        3419             1512736  3388  3389  ...   \n",
       "4             3426        3419             1512736  3388  3389  ...   \n",
       "...            ...         ...                 ...   ...   ...  ...   \n",
       "26939         3426        3419             1512736  3388  3393  ...   \n",
       "26940         3426        3419             1512736  3388  3393  ...   \n",
       "26941         3426        3419             1512736  3388  3393  ...   \n",
       "26942         3426        3419             1512736  3388  3393  ...   \n",
       "26943         3426        3419             1512736  3388  3393  ...   \n",
       "\n",
       "       bid_volume4  ask_price4  ask_volume4  bid_price5  bid_volume5  \\\n",
       "0                0           0            0           0            0   \n",
       "1                0           0            0           0            0   \n",
       "2                0           0            0           0            0   \n",
       "3                0           0            0           0            0   \n",
       "4                0           0            0           0            0   \n",
       "...            ...         ...          ...         ...          ...   \n",
       "26939            0           0            0           0            0   \n",
       "26940            0           0            0           0            0   \n",
       "26941            0           0            0           0            0   \n",
       "26942            0           0            0           0            0   \n",
       "26943            0           0            0           0            0   \n",
       "\n",
       "       ask_price5  ask_volume5  avg_price      date    avg_price2  \n",
       "0               0            0    33880.0  20200228  33880.000000  \n",
       "1               0            0    33878.3  20200228  33878.252678  \n",
       "2               0            0    33856.0  20200228  33856.020442  \n",
       "3               0            0    33848.9  20200228  33848.874663  \n",
       "4               0            0    33826.3  20200228  33826.327174  \n",
       "...           ...          ...        ...       ...           ...  \n",
       "26939           0            0    33549.3  20200228  33549.286281  \n",
       "26940           0            0    33549.2  20200228  33549.238186  \n",
       "26941           0            0    33549.2  20200228  33549.222447  \n",
       "26942           0            0    33549.2  20200228  33549.205874  \n",
       "26943           0            0    33549.2  20200228  33549.205874  \n",
       "\n",
       "[26944 rows x 45 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>symbol</th>\n",
       "      <th>last_price</th>\n",
       "      <th>prev_settle</th>\n",
       "      <th>prev_close</th>\n",
       "      <th>prev_open_interest</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>volume</th>\n",
       "      <th>...</th>\n",
       "      <th>limit_down</th>\n",
       "      <th>time</th>\n",
       "      <th>millisecond</th>\n",
       "      <th>bid_price1</th>\n",
       "      <th>bid_volume1</th>\n",
       "      <th>ask_price1</th>\n",
       "      <th>ask_volume1</th>\n",
       "      <th>avg_price</th>\n",
       "      <th>date</th>\n",
       "      <th>avg_price2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3388</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3388</td>\n",
       "      <td>3388</td>\n",
       "      <td>4412</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>08:59:00</td>\n",
       "      <td>500</td>\n",
       "      <td>3387</td>\n",
       "      <td>113</td>\n",
       "      <td>3388</td>\n",
       "      <td>65</td>\n",
       "      <td>33880.0</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33880.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3386</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>3385</td>\n",
       "      <td>5042</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>09:00:00</td>\n",
       "      <td>500</td>\n",
       "      <td>3385</td>\n",
       "      <td>522</td>\n",
       "      <td>3386</td>\n",
       "      <td>376</td>\n",
       "      <td>33878.3</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33878.252678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3383</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>3380</td>\n",
       "      <td>12230</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>09:00:01</td>\n",
       "      <td>0</td>\n",
       "      <td>3381</td>\n",
       "      <td>16</td>\n",
       "      <td>3383</td>\n",
       "      <td>732</td>\n",
       "      <td>33856.0</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33856.020442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3385</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>3380</td>\n",
       "      <td>15933</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>09:00:01</td>\n",
       "      <td>500</td>\n",
       "      <td>3383</td>\n",
       "      <td>9</td>\n",
       "      <td>3385</td>\n",
       "      <td>1045</td>\n",
       "      <td>33848.9</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33848.874663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3383</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3389</td>\n",
       "      <td>3368</td>\n",
       "      <td>21267</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>09:00:02</td>\n",
       "      <td>0</td>\n",
       "      <td>3381</td>\n",
       "      <td>26</td>\n",
       "      <td>3383</td>\n",
       "      <td>11</td>\n",
       "      <td>33826.3</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33826.327174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26939</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>3324</td>\n",
       "      <td>1949968</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>14:59:58</td>\n",
       "      <td>500</td>\n",
       "      <td>3335</td>\n",
       "      <td>341</td>\n",
       "      <td>3336</td>\n",
       "      <td>280</td>\n",
       "      <td>33549.3</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.286281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26940</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3337</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>3324</td>\n",
       "      <td>1950476</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>14:59:59</td>\n",
       "      <td>0</td>\n",
       "      <td>3337</td>\n",
       "      <td>94</td>\n",
       "      <td>3338</td>\n",
       "      <td>461</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.238186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26941</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3336</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>3324</td>\n",
       "      <td>1950644</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>14:59:59</td>\n",
       "      <td>500</td>\n",
       "      <td>3335</td>\n",
       "      <td>337</td>\n",
       "      <td>3337</td>\n",
       "      <td>175</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.222447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26942</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>3324</td>\n",
       "      <td>1950824</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>15:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>3335</td>\n",
       "      <td>336</td>\n",
       "      <td>3337</td>\n",
       "      <td>26</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.205874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26943</th>\n",
       "      <td>20200228</td>\n",
       "      <td>rb2005</td>\n",
       "      <td>3335</td>\n",
       "      <td>3426</td>\n",
       "      <td>3419</td>\n",
       "      <td>1512736</td>\n",
       "      <td>3388</td>\n",
       "      <td>3393</td>\n",
       "      <td>3324</td>\n",
       "      <td>1950824</td>\n",
       "      <td>...</td>\n",
       "      <td>3220</td>\n",
       "      <td>15:00:00</td>\n",
       "      <td>500</td>\n",
       "      <td>3335</td>\n",
       "      <td>336</td>\n",
       "      <td>3337</td>\n",
       "      <td>26</td>\n",
       "      <td>33549.2</td>\n",
       "      <td>20200228</td>\n",
       "      <td>33549.205874</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26944 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       trade_date  symbol  last_price  prev_settle  prev_close  \\\n",
       "0        20200228  rb2005        3388         3426        3419   \n",
       "1        20200228  rb2005        3386         3426        3419   \n",
       "2        20200228  rb2005        3383         3426        3419   \n",
       "3        20200228  rb2005        3385         3426        3419   \n",
       "4        20200228  rb2005        3383         3426        3419   \n",
       "...           ...     ...         ...          ...         ...   \n",
       "26939    20200228  rb2005        3335         3426        3419   \n",
       "26940    20200228  rb2005        3337         3426        3419   \n",
       "26941    20200228  rb2005        3336         3426        3419   \n",
       "26942    20200228  rb2005        3335         3426        3419   \n",
       "26943    20200228  rb2005        3335         3426        3419   \n",
       "\n",
       "       prev_open_interest  open  high   low   volume  ...  limit_down  \\\n",
       "0                 1512736  3388  3388  3388     4412  ...        3220   \n",
       "1                 1512736  3388  3389  3385     5042  ...        3220   \n",
       "2                 1512736  3388  3389  3380    12230  ...        3220   \n",
       "3                 1512736  3388  3389  3380    15933  ...        3220   \n",
       "4                 1512736  3388  3389  3368    21267  ...        3220   \n",
       "...                   ...   ...   ...   ...      ...  ...         ...   \n",
       "26939             1512736  3388  3393  3324  1949968  ...        3220   \n",
       "26940             1512736  3388  3393  3324  1950476  ...        3220   \n",
       "26941             1512736  3388  3393  3324  1950644  ...        3220   \n",
       "26942             1512736  3388  3393  3324  1950824  ...        3220   \n",
       "26943             1512736  3388  3393  3324  1950824  ...        3220   \n",
       "\n",
       "           time  millisecond  bid_price1 bid_volume1  ask_price1  ask_volume1  \\\n",
       "0      08:59:00          500        3387         113        3388           65   \n",
       "1      09:00:00          500        3385         522        3386          376   \n",
       "2      09:00:01            0        3381          16        3383          732   \n",
       "3      09:00:01          500        3383           9        3385         1045   \n",
       "4      09:00:02            0        3381          26        3383           11   \n",
       "...         ...          ...         ...         ...         ...          ...   \n",
       "26939  14:59:58          500        3335         341        3336          280   \n",
       "26940  14:59:59            0        3337          94        3338          461   \n",
       "26941  14:59:59          500        3335         337        3337          175   \n",
       "26942  15:00:00            0        3335         336        3337           26   \n",
       "26943  15:00:00          500        3335         336        3337           26   \n",
       "\n",
       "       avg_price      date    avg_price2  \n",
       "0        33880.0  20200228  33880.000000  \n",
       "1        33878.3  20200228  33878.252678  \n",
       "2        33856.0  20200228  33856.020442  \n",
       "3        33848.9  20200228  33848.874663  \n",
       "4        33826.3  20200228  33826.327174  \n",
       "...          ...       ...           ...  \n",
       "26939    33549.3  20200228  33549.286281  \n",
       "26940    33549.2  20200228  33549.238186  \n",
       "26941    33549.2  20200228  33549.222447  \n",
       "26942    33549.2  20200228  33549.205874  \n",
       "26943    33549.2  20200228  33549.205874  \n",
       "\n",
       "[26944 rows x 23 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "COLS_TO_DROP = ['exchange','symbol_in_exchange', 'bid_price2','bid_volume2','ask_price2','ask_volume2',\n",
    "                'bid_price3','bid_volume4','ask_price3','ask_volume3','bid_price4','bid_volume4',\n",
    "                'ask_price4','ask_volume4','bid_price5','bid_volume5','ask_price5','ask_volume5',\n",
    "                'prev_otm','otm', 'close', 'settle']\n",
    "df = df.drop(COLS_TO_DROP, axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Homework\n",
    "Write a function tick2min to transform tick data to minute data\n",
    "Hint:\n",
    "1. Market open and close time - better to write a function(raw2tick) to handle different assets\n",
    "2. Morning break and reopen? another function: get_trade_datetime\n",
    "3. What if no trade at the end of minute? pandas.ffill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def raw2tick(df, symbol='IH'):\n",
    "    \"\"\"\n",
    "    Input: a raw DataFrame downloaded from DataVendor\n",
    "    Returns: a DataFrame with data only in market open time\n",
    "    \"\"\"\n",
    "    # Code begins\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "NIGHT_ABNORMAL_DATES = []\n",
    "def get_trade_time(symbol):\n",
    "    \"\"\" \n",
    "    Return trade datettime includes: night/day open, close datetime\n",
    "    if no break in morning, moring break start/end datetime would be set as monring_trade_end_time\n",
    "    \"\"\"\n",
    "    # Code begins\n",
    "    if trade_date in NIGHT_ABNORMAL_DATES:\n",
    "        \n",
    "    \n",
    "    return (night_trade_start_datetime, night_trade_end_datetime, morning_trade_start_datetime,\n",
    "    morning_break_start_datetime, morning_break_end_datetime, morning_trade_end_datetime,\n",
    "    afternoon_trade_start_datetime, afternoon_trade_end_datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tick2min(df):\n",
    "    \"\"\"\n",
    "    Input: a raw DataFrame of market tick data\n",
    "    Returns: a DataFrame with Open High Low Close price sampled by 1 minute\n",
    "    resample\n",
    "    Note:\n",
    "    1. minute begins: 09:01:00,000, should be included in first minute\n",
    "    \n",
    "    \"\"\"\n",
    "    # Code begins\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So you can write your own pipeline to handle all kinds of market data from now"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
